/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package sv.com.intesal.proyecto.sql;

/**
 *
 * @author ejerez
 */
public class SQLProyecto_ResumenGeneral {

    public static final String getResumenTotal =
            " SELECT ms.id_dm,m.nombre_m,cat.nombre_cat,sc.nombre_su,tm.nombre_tm, "
            + " (select nombre_p from proyecto where id_p=?) as nombre_p, "
            + " (select fecha_inicio_p from proyecto where id_p=?) as fecha_inicio_p, "
            + " (select fecha_finalizacion_p from proyecto where id_p=?) as fecha_finalizacion_p, "
            + " (select descripcion_p from proyecto where id_p=?) as descripcion_p, "
            + " (select direccion_p from proyecto where id_p=?) as direccion_p, "
            + " COALESCE( ( "
            + " SELECT COALESCE(sum(d.cantidad_d),0) "
            + " FROM proyecto as p "
            + " left join devolucion_proyecto as dp on p.id_p=dp.id_p "
            + " left join deta_devolucion_proyecto as d on dp.id_dp=d.id_dp "
            + " where p.id_p=? and ms.id_dm=d.id_dm and dp.estado_dp='AC' "
            + " group by d.id_dm "
            + " ),0)  as total_devolucion, "
            + " COALESCE( ( "
            + " SELECT COALESCE(sum(d.cantidad_d),0) "
            + " FROM proyecto as p "
            + " left join salida_proyecto as sp on p.id_p=sp.id_p "
            + " left join deta_salida_proyecto as d on sp.id_sp=d.id_sp	"
            + " where p.id_p=? and ms.id_dm=d.id_dm and sp.estado_sp='AC' "
            + " group by d.id_dm "
            + " ),0)  as total_salida, "
            + " COALESCE( ( "
            + " SELECT COALESCE(sum(d.cantidad_d),0) "
            + " FROM proyecto as p "
            + " left join utilizacion_proyecto as up on p.id_p=up.id_p "
            + " left join deta_utilizacion_proyecto as d on up.id_up=d.id_up "
            + " where p.id_p=? and ms.id_dm=d.id_dm and up.estado_up='AC' "
            + " group by d.id_dm "
            + " ),0)  as total_utilizacion, "
            + " COALESCE( ( "
            + " SELECT COALESCE(sum(d.cantidad_d),0) "
            + " FROM proyecto as p "
            + " left join deta_proyecto as d on p.id_p=d.id_p "
            + " where p.id_p=? and ms.id_dm=d.id_dm "
            + " group by d.id_dm "
            + " ),0)  as total_presupuesto, "
              + " COALESCE( ( " 
              + " SELECT COALESCE(sum(d.cantidad_d),0) " 
              + " FROM proyecto as p " 
              + " left join devolucion_proyecto as dp on p.id_p=dp.id_p " 
              + " left join deta_devolucion_proyecto as d on dp.id_dp=d.id_dp " 
              + " where p.id_p=? and ms.id_dm=d.id_dm and dp.estado_dp='AC' " 
              + " group by d.id_dm " 
              + " ),0)  - "
              + " COALESCE( ( " 
              + " SELECT COALESCE(sum(d.cantidad_d),0) " 
              + " FROM proyecto as p "
              + " left join salida_proyecto as sp on p.id_p=sp.id_p " 
              + " left join deta_salida_proyecto as d on sp.id_sp=d.id_sp "	
              + " where p.id_p=? and ms.id_dm=d.id_dm and sp.estado_sp='AC' " 
              + " group by d.id_dm "
              + " ),0)  - "
              + " COALESCE( ( " 
              + " SELECT COALESCE(sum(d.cantidad_d),0) " 
              + " FROM proyecto as p " 
              + " left join utilizacion_proyecto as up on p.id_p=up.id_p " 
              + " left join deta_utilizacion_proyecto as d on up.id_up=d.id_up "
              + " where p.id_p=? and ms.id_dm=d.id_dm and up.estado_up='AC' " 
              + " group by d.id_dm " 
              + " ),0)  - "
              + " COALESCE( ( " 
              + " SELECT COALESCE(sum(d.cantidad_d),0) " 
              + " FROM proyecto as p "
              + " left join deta_proyecto as d on p.id_p=d.id_p "
              + " where p.id_p=? and ms.id_dm=d.id_dm " 
              + " group by d.id_dm " 
              + " ),0) "  
               + " as total "
            + " from material_sucursal ms "
            + " inner join material as m on m.id_m=ms.id_m "
            + " inner join sub_categoria as sc  on sc.id_su=m.id_su "
            + " inner join categoria as cat on cat.id_cat=sc.id_cat "
            + " inner join tipo_material as tm on tm.codigo_tm=m.codigo_tm "
            + " inner join material_tipo_proyecto as mtp on ms.id_dm=mtp.id_dm and mtp.id_tp=? "
            + " order by m.nombre_m ";
}



/*
        " SELECT ms.id_dm,m.nombre_m,cat.nombre_cat,sc.nombre_su,tm.nombre_tm, "
            + " (select nombre_p from proyecto where id_p=?) as nombre_p, "
            + " (select fecha_inicio_p from proyecto where id_p=?) as fecha_inicio_p, "
            + " (select fecha_finalizacion_p from proyecto where id_p=?) as fecha_finalizacion_p, "
            + " (select descripcion_p from proyecto where id_p=?) as descripcion_p, "
            + " (select direccion_p from proyecto where id_p=?) as direccion_p, "
            + " COALESCE( ( "
            + " SELECT COALESCE(sum(d.cantidad_d),0) "
            + " FROM proyecto as p "
            + " left join devolucion_proyecto as dp on p.id_p=dp.id_p "
            + " left join deta_devolucion_proyecto as d on dp.id_dp=d.id_dp "
            + " where p.id_p=? and ms.id_dm=d.id_dm and dp.estado_dp='AC' "
            + " group by d.id_dm "
            + " ),0)  as total_devolucion, "
            + " COALESCE( ( "
            + " SELECT COALESCE(sum(d.cantidad_d),0) "
            + " FROM proyecto as p "
            + " left join salida_proyecto as sp on p.id_p=sp.id_p "
            + " left join deta_salida_proyecto as d on sp.id_sp=d.id_sp	"
            + " where p.id_p=? and ms.id_dm=d.id_dm and sp.estado_sp='AC' "
            + " group by d.id_dm "
            + " ),0)  as total_salida, "
            + " COALESCE( ( "
            + " SELECT COALESCE(sum(d.cantidad_d),0) "
            + " FROM proyecto as p "
            + " left join utilizacion_proyecto as up on p.id_p=up.id_p "
            + " left join deta_utilizacion_proyecto as d on up.id_up=d.id_up "
            + " where p.id_p=? and ms.id_dm=d.id_dm and up.estado_up='AC' "
            + " group by d.id_dm "
            + " ),0)  as total_utilizacion, "
            + " COALESCE( ( "
            + " SELECT COALESCE(sum(d.cantidad_d),0) "
            + " FROM proyecto as p "
            + " left join deta_proyecto as d on p.id_p=d.id_p "
            + " where p.id_p=? and ms.id_dm=d.id_dm "
            + " group by d.id_dm "
            + " ),0)  as total_presupuesto, "
            + " ( "
            + " (COALESCE( ( "
            + " SELECT COALESCE(sum(d.cantidad_d),0) --devolucion "
            + " FROM proyecto as p "
            + " left join devolucion_proyecto as dp on p.id_p=dp.id_p "
            + " left join deta_devolucion_proyecto as d on dp.id_dp=d.id_dp "
            + " where p.id_p=? and ms.id_dm=d.id_dm and dp.estado_dp='AC' "
            + " group by d.id_dm "
            + " ),0)) "
            + " - "
            + " (COALESCE( ( "
            + " SELECT COALESCE(sum(d.cantidad_d),0) --presupuesto "
            + " FROM proyecto as p "
            + " left join deta_proyecto as d on p.id_p=d.id_p "
            + " where p.id_p=? and ms.id_dm=d.id_dm "
            + " group by d.id_dm "
            + " ),0)) "
            + " - "
            + " (COALESCE( ( "
            + " SELECT COALESCE(sum(d.cantidad_d),0) --salida "
            + " FROM proyecto as p "
            + " left join salida_proyecto as sp on p.id_p=sp.id_p "
            + " left join deta_salida_proyecto as d on sp.id_sp=d.id_sp	"
            + " where p.id_p=? and ms.id_dm=d.id_dm and sp.estado_sp='AC' "
            + " group by d.id_dm "
            + " ),0)) "
            + " - "
            + " (COALESCE( ( "
            + " SELECT COALESCE(sum(d.cantidad_d),0) --utilizacion "
            + " FROM proyecto as p "
            + " left join utilizacion_proyecto as up on p.id_p=up.id_p "
            + " left join deta_utilizacion_proyecto as d on up.id_up=d.id_up "
            + " where p.id_p=? and ms.id_dm=d.id_dm and up.estado_up='AC' "
            + " group by d.id_dm "
            + " ),0)) "
            + " ) as total "
            + " from material_sucursal ms "
            + " inner join material as m on m.id_m=ms.id_m "
            + " inner join sub_categoria as sc  on sc.id_su=m.id_su "
            + " inner join categoria as cat on cat.id_cat=sc.id_cat "
            + " inner join tipo_material as tm on tm.codigo_tm=m.codigo_tm "
            + " inner join material_tipo_proyecto as mtp on ms.id_dm=mtp.id_dm and mtp.id_tp=? "
            + " order by m.nombre_m ";
 */